package com.aaa.project.zhreport.controller.dto;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.text.StrBuilder;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import com.aaa.project.zhreport.constants.FieldTypeConstant;
import com.aaa.project.zhreport.constants.OperatorTypeConstant;
import lombok.Data;
import org.springframework.web.multipart.MultipartFile;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author baibin
 * @version 1.0
 * @date 2025/4/9 10:07
 * @description YwfxDataApplyReqDTO
 */
@Data
public class YwfxDataApplyReqDTO {

    /** 工单编号 order_number */
    private String orderNumber;

    /** 模板id template_id */
    private Long templateId;

    /** 模板名称 */
    private String templateName;

    // 临时表名
    private String tempTableName;

    /** 附件 */
    private MultipartFile attachment;

    /** 申请备注 apply_remark */
    private String remark;

    /** 审批人id approver_id */
    private List<Long> userIds;

    private String createBy;

    private String updateBy;

    /**
     * 申请审批变量
     */
    private List<YwfxDataApplyExecVariablesDTO> variablesList;

    private String sql;

    // 条数限制
    private Long rowLimit;

    // 生成的sql
    private String previewSql;

    public String convertSQL(String execSql, String dbType) {
        if (CollUtil.isEmpty(variablesList) && rowLimit == null)  {
            return execSql;
        }

        // Step 1: 提取原始 WHERE 条件
        String existingWhere = extractWhereCondition(execSql);
        StringBuilder newWhereBuilder = new StringBuilder(existingWhere);

        for (YwfxDataApplyExecVariablesDTO variable : variablesList) {
            String condition = buildVariableCondition(variable, dbType);
            if (condition != null) {
                if (newWhereBuilder.length() > 0) {
                    newWhereBuilder.append(" AND ").append(condition).append(" ");
                } else {
                    newWhereBuilder.append(condition).append(" ");
                }
            }
        }

        String newWhereClause = newWhereBuilder.toString();

        // Step 2: 判断是否原 SQL 有 WHERE
        if (execSql.toLowerCase().contains("where")) {
            // 已有 WHERE，替换掉
            execSql = replaceWhereCondition(execSql, newWhereClause);
        } else {
            // 如果没有 WHERE，就插入到 FROM 子句之后
            if (StrUtil.isNotBlank(newWhereClause)) {
                Pattern pattern = Pattern.compile(
                        "\\bfrom\\b.*?(?=\\b(where|group\\s+by|order\\s+by|limit)\\b|$)",
                        Pattern.CASE_INSENSITIVE | Pattern.DOTALL
                );
                Matcher matcher = pattern.matcher(execSql);

                if (matcher.find()) {
                    int insertPos = matcher.end();
                    StrBuilder strBuilder = StrUtil.strBuilder();
                    strBuilder.append(execSql, 0, insertPos)
                            .append(" where ")
                            .append(newWhereClause)
                            .append(execSql, insertPos, execSql.length());
                    execSql = strBuilder.toString();
                } else {
                    // 没有 FROM，直接加 WHERE（或按需处理）
                    execSql += " where " + newWhereClause;
                }
            }
        }

        // 检查sql中是否有limit限制，如果有则替换掉，没有就追加
        String sql = replaceOrAppendPagination(execSql, rowLimit, dbType);
//        return sql.toLowerCase();
        return sql;
    }

    /**
     * 构建变量条件
     *
     * @param variable
     * @param dbType
     * @return
     */
    private String buildVariableCondition(YwfxDataApplyExecVariablesDTO variable, String dbType) {
        if (variable == null || StrUtil.isBlank(variable.getFieldName())) {
            return null;
        }

        StringBuilder condition = new StringBuilder();
        condition.append(variable.getFieldName());

        String operator = StrUtil.isNotBlank(variable.getOperator()) ? variable.getOperator() : "=";
        condition.append(" ").append(operator).append(" ");

        if ("between".equalsIgnoreCase(operator)) {
            // 判断字符类型
            if (FieldTypeConstant.STRING.equals(variable.getFieldType())) {
                condition.append("'").append(variable.getStartValue()).append("' AND '").append(variable.getEndValue()).append("'");
            } else {
                condition.append(variable.getStartValue()).append(" AND ").append(variable.getEndValue());
            }
        } else if (FieldTypeConstant.STRING.equals(variable.getFieldType())) {
            condition.append("'").append(variable.getValue()).append("'");
        } else if (FieldTypeConstant.NUMBER.equals(variable.getFieldType())) {
            condition.append(variable.getValue());
        } else if (FieldTypeConstant.DATE.equals(variable.getFieldType())) {
            Date[] dateRange = variable.getDateRange();
            if ("between".equalsIgnoreCase(operator)) {
                String startDate = DateUtil.beginOfDay(dateRange[0]).toString();
                String endDate = DateUtil.endOfDay(dateRange[1]).toString();
                if ("ORACLE".equals(dbType)) {
                    return String.format("%s BETWEEN TO_DATE('%s', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('%s', 'YYYY-MM-DD HH24:MI:SS')",
                            variable.getFieldName(), startDate, endDate);
                } else {
                    return String.format("%s BETWEEN '%s' AND '%s'", variable.getFieldName(), startDate, endDate);
                }
            } else {
                String dateStr = DateUtil.format(variable.getDate(), DatePattern.NORM_DATE_PATTERN);
                if ("ORACLE".equals(dbType)) {
                    return String.format("%s = TO_DATE('%s', 'YYYY-MM-DD')", variable.getFieldName(), dateStr);
                } else {
                    return String.format("%s = '%s'", variable.getFieldName(), dateStr);
                }
            }
        }
        return condition.toString();
    }



    /**
     * 提取 SQL 中 WHERE 条件内容
     */
    private String extractWhereCondition(String sql) {
        if (sql == null || !sql.toLowerCase().contains("where")) {
            return "";
        }

        Pattern pattern = Pattern.compile("\\bwhere\\b(.*?)(?=(\\bgroup\\b|\\border\\b|\\blimit\\b|$))",
                Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        Matcher matcher = pattern.matcher(sql);

        if (matcher.find()) {
            return matcher.group(1).trim();
        }
        return "";
    }

    /**
     * 替换 SQL 中的 WHERE 条件
     */
    private String replaceWhereCondition(String sql, String newWhereCondition) {
        if (sql == null || !sql.toLowerCase().contains("where")) {
            int fromIndex = sql.toLowerCase().indexOf("from");
            if (fromIndex > 0) {
                StrBuilder builder = StrUtil.strBuilder();
                builder.append(sql, 0, fromIndex)
                        .append(" where ")
                        .append(newWhereCondition)
                        .append(sql, fromIndex, sql.length());
                return builder.toString();
            }
            return sql;
        }

        // 使用正则提取并替换完整的 WHERE 子句（包含其后的内容）
        Pattern pattern = Pattern.compile(
                "(\\bwhere\\b)(.*?)(?=\\b(group\\s+by|order\\s+by|limit|;)|$)",
                Pattern.CASE_INSENSITIVE | Pattern.DOTALL
        );
        Matcher matcher = pattern.matcher(sql);

        if (matcher.find()) {
            String originalWhereClause = matcher.group(0); // 包括 WHERE 和后面条件
            // 构建新的 WHERE 子句，注意保留原始的 WHERE 关键字大小写
            String newClause = "where " + newWhereCondition + " ";

            // 使用正则替换避免拼接错误
            sql = sql.replaceFirst(Pattern.quote(originalWhereClause), newClause);
        }

        return sql;
    }

    /**
     * 追加或替换分页
     *
     * @param sql
     * @param rowLimit
     * @param dbType
     * @return
     */
    private String replaceOrAppendPagination(String sql, Long rowLimit, String dbType) {
        if (rowLimit == null || rowLimit <= 0) {
            return sql;
        }

        switch (dbType) {
            case "PGSQL":
                // PostgreSQL 使用 LIMIT
                Pattern limitPattern = Pattern.compile("\\blimit\\b\\s*\\d+", Pattern.CASE_INSENSITIVE);
                Matcher matcher = limitPattern.matcher(sql);
                if (matcher.find()) {
                    // 替换已有的 LIMIT
                    sql = matcher.replaceAll("limit " + rowLimit);
                } else {
                    // 没有则追加到最后
                    sql = StrUtil.strBuilder(sql).append(" limit ").append(rowLimit.toString()).toString();
                }
                break;
            case "ORACLE":
                // 匹配 ROWNUM <= ?
                Pattern rownumPattern = Pattern.compile("\\brownum\\s*<=\\s*\\d+\\b", Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
                Matcher rownumMatcher = rownumPattern.matcher(sql);

                if (rownumMatcher.find()) {
                    // 替换已有的 ROWNUM 值
                    sql = sql.replaceAll("(?i)rownum\\s*<=\\s*\\d+", "rownum <= " + rowLimit);
                } else {
                    // 没有 ROWNUM，判断是否有 WHERE
                    Pattern wherePattern = Pattern.compile("\\bwhere\\b(.*?)(?=\\border\\s+by\\b|$)", Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
                    Matcher whereMatcher = wherePattern.matcher(sql);

                    if (whereMatcher.find()) {
                        // 已有 WHERE，在其后追加 AND ROWNUM <= ?
                        String existingWhere = whereMatcher.group(0).trim();  // 去除头尾空格防止重复
                        StrBuilder newWhere = StrUtil.strBuilder()
                                .append(existingWhere)
                                .append(" and rownum <= ")
                                .append(rowLimit.toString());
                        // 替换原有 WHERE 子句，并保留后续内容
                        sql = sql.replaceFirst(Pattern.quote(existingWhere), newWhere.toString());
                    } else {
                        // 没有 WHERE
                        Pattern fromPattern = Pattern.compile("\\bfrom\\b.*?(?=\\b(where|group\\s+by|order\\s+by|limit)\\b|$)",
                                Pattern.CASE_INSENSITIVE);
                        Matcher fromMatcher = fromPattern.matcher(sql);

                        StrBuilder strBuilder = StrUtil.strBuilder();

                        if (fromMatcher.find()) {
                            int insertPos = fromMatcher.end();
                            strBuilder.append(sql, 0, insertPos)
                                    .append(" where rownum <= ")
                                    .append(rowLimit.toString())
                                    .append(" ")
                                    .append(sql, insertPos, sql.length());
                        } else {
                            // 如果连 FROM 都没有，直接附加到末尾（或按需处理）
                            strBuilder.append(sql).append(" where rownum <= ")
                                    .append(rowLimit.toString()).append(" ");
                        }
                        sql = strBuilder.toString();
                    }
                }
                break;
            default:
                // 默认使用 LIMIT，兼容 MySQL、SQLite 等数据库
                Pattern defaultLimitPattern = Pattern.compile("\\blimit\\b\\s*\\d+", Pattern.CASE_INSENSITIVE);
                Matcher defaultMatcher = defaultLimitPattern.matcher(sql);
                if (defaultMatcher.find()) {
                    // 替换已有的 LIMIT
                    sql = defaultMatcher.replaceAll("limit " + rowLimit);
                } else {
                    // 使用 StrBuilder 追加 LIMIT
                    sql = StrUtil.strBuilder(sql).append(" limit ").append(rowLimit.toString()).toString();
                }
                break;
        }
        return sql;
    }

    public String getSqlLimit(String sql, Long rowLimit, String dbType) {
        String sqlStr = replaceOrAppendPagination(sql, rowLimit, dbType);
        return sqlStr;
    }


}
